File Format Rules
Extract the Required .csv import file layout.
All File Layouts are provided in Excel in a .csv format. Each file layout will include all available fields entered into the first line of the file. The following steps produces an excel spreadsheet with the necessary fields.
- Select the Mapper Icon from the Toolbar
- Select File, Open and select the Activity Number
- Select Help, CSV Template
When making minor changes to the .csv file, the file can be opened by using Notepad.
- The column field names within the .csv file cannot be changed as this is the critical link to the corresponding fields.
- Fields do not have to be in any particular order. The system just requires that all field headings are present and correct.
- Dates can be entered using one of the following formats:
- dd/mm/yy
- dd/mm/yyyy
- yyyy-mm-dd
- If a date field contains an invalid date format an error is generated and is reported in the Summary Report, the record is not imported and is sent to the exception file.
- Field headings can be upper or lower case.
- When importing information for a field that does not match an existing drop down choice list, and error is generated and reported in the Summary Report, the record is not imported and is sent to the Exception file.
- If a field is left blank no change is made to the field.
- If a field is to be cleared or blanked out, enter *blank in the field in the .csv field, but only if the field is not mandatory.
- For fields that contain a value with leading zeros, the cell must be formatted as a text field.
- Cells that contain data with leading zeros, once saved and re-opened the leading zeros are not displayed but are still present in the file. Opening the .csv file in Notepad will verify that the leading zeros are present in the file.
- For fields that contain dates the cell must be formatted as a date field.
- Character fields entered with leading spaces and no quotes will have spaces removed.
- Character fields entered with leading spaces enclosed in quotes will result in the leading spaces kept.
- Character fields containing commas must be encapsulated in quotes.
- Records are separated by either, a return and line feed (0x0D 0x0A in SASCII) or a line feed (0x0A in ASCII)
- Fields are separated by commas.
-
Fields may be delimited with double quotes. This is optional except in the following cases, in which case it is mandatory to use double quotes:-
The field value contains comma
The field value contains leading or trailing blanks
The field value contains double quotes. In this case the double quotes should be represented by a pair of double quotes. - The use of double quotes is not dependent on the data type represented by the field. A numeric field can be delimited by double quotes.
- If a field is not delimited with double quotes, then leading and trailing spaces are stripped off.
Example:-
|
All Trailing spaces will be truncated before inserting into the database.
Example:-
|
All spreadsheets must be save in .csv format. When saving the .csv file a message appears warning that some features may not be compatible. Select Y to continue saving and ignoring the message, then close the file and accept Y for each prompt.